import json
from typing import Dict, List, Optional
import mysql.connector
from mysql.connector import Error

class ProductDB:
    def __init__(self, db_config: Dict):
        self.db_config = db_config
        self.connection = self._create_connection()

    def _create_connection(self):
        """创建数据库连接"""
        try:
            return mysql.connector.connect(
                host=self.db_config['host'],
                port=self.db_config['port'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                database=self.db_config['database']
            )
        except Error as e:
            print(f"Error while connecting to MySQL: {e}")
            return None

    def get_product(self, product_id: int) -> Optional[Dict]:
        """根据ID获取单个商品"""
        query = f"SELECT * FROM {self.db_config['product_table']} WHERE id = %s"
        cursor = self.connection.cursor(dictionary=True)
        cursor.execute(query, (product_id,))
        return cursor.fetchone()

    def search_products(self, keyword: str, limit: int = 10) -> List[Dict]:
        """根据关键词搜索商品"""
        query = f"SELECT * FROM {self.db_config['product_table']} WHERE name LIKE %s LIMIT %s"
        cursor = self.connection.cursor(dictionary=True)
        cursor.execute(query, (f"%{keyword}%", limit))
        return cursor.fetchall()

    def get_aligned_products(self, product_id: int) -> List[Dict]:
        """获取商品的对齐信息"""
        query = f"SELECT * FROM {self.db_config['alignment_table']} WHERE product_id = %s"
        cursor = self.connection.cursor(dictionary=True)
        cursor.execute(query, (product_id,))
        return cursor.fetchall()

    def update_product(self, product_id: int, update_data: Dict):
        """更新商品信息"""
        set_clause = ", ".join([f"{k} = %s" for k in update_data.keys()])
        query = f"UPDATE {self.db_config['product_table']} SET {set_clause} WHERE id = %s"
        values = list(update_data.values()) + [product_id]
        cursor = self.connection.cursor()
        cursor.execute(query, tuple(values))
        self.connection.commit()

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

if __name__ == "__main__":
    # 示例用法
    config = {
        'host': 'rm-2zea4lxfy2974xc9xso.mysql.rds.aliyuncs.com',
        'port': 3306,
        'user': 'ry',
        'password': 'Qwer1111',
        'database': 'mingxiangzhu_mall',
        'product_table': 'yx_store_product',
        'alignment_table': 'yx_store_product_alignment'
    }
    
    db = ProductDB(config)
    try:
        # 获取商品信息
        product = db.get_product(1)
        print(json.dumps(product, indent=2, ensure_ascii=False))
        
        # 搜索商品
        products = db.search_products('宠物')
        print(json.dumps(products, indent=2, ensure_ascii=False))
        
        # 获取商品对齐信息
        aligned_products = db.get_aligned_products(1)
        print(json.dumps(aligned_products, indent=2, ensure_ascii=False))
    finally:
        db.close()
